﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace QLSV
{
    public partial class ThongTinLop : Form
    {
        public ThongTinLop()
        {
            InitializeComponent();
        }

        private SqlConnection Con;
        private DataTable Dt = new DataTable("Lop");
        private DataTable DtL = new DataTable("Lop");
        private DataTable DtGV = new DataTable("GiaoVien");
        private SqlDataAdapter Da = new SqlDataAdapter();
        private Boolean Kt;

        private void Connect()
        {
            String Cn = "Data Source=LENOVOY510\\SQLEXPRESS;Initial Catalog=QLSV;Integrated Security=True";
            try
            {
                Con = new SqlConnection(Cn);
                Con.Open();
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Không Thể Kết Nối Đến Cơ Sở Dữ Liệu!", "Lỗi!", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
        }

        private void DisConnect()
        {
            Con.Close();
            Con.Dispose();
            Con = null;
        }

        private void GetData()
        {
            SqlCommand Com = new SqlCommand();
            Com.Connection = Con;

            Com.CommandType = CommandType.Text;
            Com.CommandText = @"Select MaGV as N'Mã Giáo Viên',
                                          Ho as N'Họ',
                                          Ten as N'Tên',
                                          GioiTinh as N'Giới Tính',
                                          NgaySinh as N'Ngày Sinh'                                  
                                          from GiaoVien";
            Da.SelectCommand = Com;
            Da.Fill(DtGV);
            dgvttgv.DataSource = DtGV;

            dgvttgv.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttgv.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttgv.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttgv.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }

        private void GetData1()
        {
            SqlCommand Com = new SqlCommand();
            Com.Connection = Con;
            Com.CommandType = CommandType.Text;
            Com.CommandText = "Select * from Lop";
            Da.SelectCommand = Com;
            Da.Fill(DtL);

            Com.CommandType = CommandType.Text;
            Com.CommandText = "Select * from GiaoVien";
            Da.SelectCommand = Com;
            Da.Fill(DtGV);

            Com.CommandType = CommandType.Text;
            Com.CommandText = @"Select MaLop as N'Mã Lớp',
                                          TenLop as N'Tên Lớp',
                                          SiSo as N'Sĩ Số',
                                          Ho + ' ' + Ten as N'Giáo Viên Chủ Nhiệm',
	                                      NH as N'Năm Học',
                                          ChuyenNganh as N'Chuyên Ngành'
                                   from Lop left join ChuNhiem
                                          on Lop.MaLop = ChuNhiem.CNMaLop
                                            left join GiaoVien
                                          on ChuNhiem.CNMaCN = GiaoVien.MaGV";

            Da.SelectCommand = Com;
            Da.Fill(Dt);
            dgvttl.DataSource = Dt;

            dgvttl.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttl.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvttl.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttl.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttl.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvttl.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }

        private void GetData2()
        {
            SqlCommand Com = new SqlCommand();
            Com.Connection = Con;
            Com.CommandType = CommandType.Text;
            Com.CommandText = "Select * from Lop";
            Da.SelectCommand = Com;
            Da.Fill(DtL);

            Com.CommandType = CommandType.Text;
            Com.CommandText = "Select * from GiaoVien";
            Da.SelectCommand = Com;
            Da.Fill(DtGV);

            Com.CommandType = CommandType.Text;
            Com.CommandText = @"Select MaLop as N'Mã Lớp',
                                          TenLop as N'Tên Lớp',
                                          SiSo as N'Sĩ Số',
                                          Ho + ' ' + Ten as N'Giáo Viên Chủ Nhiệm',
	                                      NH as N'Năm Học',
                                          ChuyenNganh as N'Chuyên Ngành'
                                   from Lop left join ChuNhiem
                                          on Lop.MaLop = ChuNhiem.CNMaLop
                                            left join GiaoVien
                                          on ChuNhiem.CNMaCN = GiaoVien.MaGV
								   where NH = '2013 - 2014' and HK like '2' + '%' or HK = N'Năm 4'";

            Da.SelectCommand = Com;
            Da.Fill(Dt);
            dgvttl.DataSource = Dt;

            dgvttl.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttl.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvttl.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttl.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
            dgvttl.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvttl.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }

        private void ThongTinLop_Load(object sender, EventArgs e)
        {
            Connect();
            GetData();
            GetData2();
            Binding();
        }

        private void clicktc_Click(object sender, EventArgs e)
        {
            Dt.Clear();
            GetData1();
        }

        private void clickht_Click(object sender, EventArgs e)
        {
            Dt.Clear();
            GetData2();
        }

        private void clickthoat_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn Có Muốn Thoát Không?", "Thoát!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                Close();
                Dispose();
            }
        }

        private void Binding()
        {
            txtml.DataBindings.Clear();
            txtml.DataBindings.Add("Text", dgvttl.DataSource, "Mã Lớp");
            txtss.DataBindings.Clear();
            txtss.DataBindings.Add("Text", dgvttl.DataSource, "Sĩ Số");
            txtgvcn.DataBindings.Clear();
            txtgvcn.DataBindings.Add("Text", dgvttl.DataSource, "Giáo Viên Chủ Nhiệm");
            txtmgv.DataBindings.Clear();
            txtmgv.DataBindings.Add("Text", dgvttgv.DataSource, "Mã Giáo Viên");
        }

        private void clickhuy_Click(object sender, EventArgs e)
        {
            txtmgv.Text = "";
            txtss.Text = "";
        }

        private bool KiemTra()
        {
            Kt = true;
            int t = Convert.ToInt32(txtss.Text);

            if (t <= 0 && t >= 250)
            {
                MessageBox.Show("Mời Nhập Lại Sĩ Số!", "Thông Báo!", MessageBoxButtons.OK);
                Kt = false;
            }

            return Kt;
        }

        private void clicksua_Click(object sender, EventArgs e)
        {
            if (txtml.Text.Length > 0)
            {
                KiemTra();
                if (Kt == true)
                {
                    DataRow DtR = Dt.Select("[Mã Lớp] = " + (txtml.Text))[0];
                    DtR.BeginEdit();
                    DtR["Sĩ Số"] = Convert.ToInt32(txtss.Text);
                    DtR.EndEdit();
                    SqlCommand ComUpdate = new SqlCommand();
                    ComUpdate.Connection = Con;
                    ComUpdate.CommandType = CommandType.Text;
                    ComUpdate.CommandText = @"Update Lop set SiSo = @SiSo                                                                                                           
                                                                 where MaLop = @MaLop";
                    ComUpdate.Parameters.Add("@MaLop", SqlDbType.NChar, 10, "Mã Lớp");
                    ComUpdate.Parameters.Add("@SiSo", SqlDbType.NVarChar, 30, "Sĩ Số");
                    Da.UpdateCommand = ComUpdate;
                    Da.Update(Dt);

                    DataRow DtR1 = Dt.Select("[Mã Lớp] = " + (txtml.Text))[0];
                    DtR.BeginEdit();
                    DtR["Giáo Viên Chủ Nhiệm"] = txtmgv.Text;
                    DtR.EndEdit();
                    SqlCommand ComUpdate1 = new SqlCommand();
                    ComUpdate1.Connection = Con;
                    ComUpdate1.CommandType = CommandType.Text;
                    ComUpdate1.Parameters.Clear();
                    ComUpdate1.CommandText = @"Update ChuNhiem set CNMaCN = @MaGV                                                                                                    
                                                                where CNMaLop = @MaLop";
                    ComUpdate1.Parameters.Add("@MaLop", SqlDbType.NChar, 10, "Mã Lớp");
                    ComUpdate1.Parameters.Add("@MaGV", SqlDbType.NChar, 10, "Giáo Viên Chủ Nhiệm");
                    Da.UpdateCommand = ComUpdate1;
                    Da.Update(Dt);

                    MessageBox.Show("Bạn Đã Sửa Thành Công!", "Thông Báo!", MessageBoxButtons.OK);
                    Close();
                    Dispose();
                    ThongTinLop TTL = new ThongTinLop();
                    TTL.Show();
                }
            }
        }
    }
}
